package com.example.personalizededucationsystem.repository;

import com.example.personalizededucationsystem.model.Question;
import com.example.personalizededucationsystem.utils.ApprovalStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Collection;
import java.util.List;

public interface QuestionRepository extends JpaRepository<Question, Long> {

    @Query(value = "SELECT q.* FROM questions q JOIN question_kps qk ON q.question_id = qk.question_id JOIN knowledge_points kp ON qk.kp_id = kp.kp_id WHERE q.status = 'APPROVED' AND kp.subject = :subject AND q.type IN ('SINGLE_CHOICE', 'MULTIPLE_CHOICE') GROUP BY q.question_id ORDER BY RAND() LIMIT :count", nativeQuery = true)
    List<Question> findRandomApprovedQuestionsBySubject(@Param("subject") String subject, @Param("count") int count);

    @Query("SELECT q FROM Question q JOIN q.knowledgePoints kp WHERE q.status = 'APPROVED' AND kp.kpId IN :kpIds")
    List<Question> findApprovedQuestionsByKnowledgePointIds(@Param("kpIds") Collection<Long> kpIds, Pageable pageable);

    @Query("SELECT q FROM Question q WHERE q.status = :status AND (:keyword IS NULL OR q.content LIKE %:keyword%)")
    Page<Question> findQuestionsByStatusAndKeyword(@Param("status") ApprovalStatus status, @Param("keyword") String keyword, Pageable pageable);

    @Query("SELECT DISTINCT q FROM Question q JOIN q.knowledgePoints kp WHERE q.status = :status AND (:keyword IS NULL OR :keyword = '' OR q.content LIKE CONCAT('%', :keyword, '%')) AND kp.subject = :subject")
    Page<Question> findQuestionsByStatusKeywordAndSubject(@Param("status") ApprovalStatus status, @Param("keyword") String keyword, @Param("subject") String subject, Pageable pageable);


}